USE [kymedi2b2data]
GO

/****** Object:  UserDefinedFunction [dbo].[OR_CLAUSE]    Script Date: 10/22/2012 3:08:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[OR_CLAUSE] (@or_items int, @or_items_single_fact bit, @total_item_occur int, @prev_panel_timing varchar(20), 
@prev_item_factTable varchar(50), @factTable varchar(50), @panelTiming varchar(20), @panel_date_from varchar(10), @panel_date_to varchar(10), 
@factTableColumn varchar(50), @columnName varchar(50), @operator varchar(20), @dimcode varchar(700), @modifier_cd int, @modifier_dim varchar(700), 
@meta_id int, @constrain_operator varchar(10), @constrain_value varchar(50), @constrain_type varchar(10))
RETURNS varchar(max)
AS
BEGIN
	
	declare @c varchar(10) 
	set @c = 'cpp.'
	declare @sql varchar(max) = ''
	
	set @sql = ') ' + char(13) + ' LEFT OUTER JOIN '
	if @or_items_single_fact = 0 and @total_item_occur = 1
	begin
		
		if @factTable = 'concept_dimension'
		begin
			
			/* Join Predicate */
			set @sql = @sql + 'dbo.OBSERVATION_FACT or'+cast(@or_items as varchar(2))+char(13)
					        + 'on '
			if @prev_panel_timing = 'SAME' 
				begin
					set @sql = @sql + 'cpp.encounter_num = or'+cast(@or_items as varchar(2))+'.encounter_num'
				end
			else 
				begin
					set @sql = @sql + 'cpp.patient_num = or'+cast(@or_items as varchar(2))+'.patient_num'
				end
			
			/* concept subquery predicate */
			set @sql = @sql + 'AND or'+cast(@or_items as varchar(2))+'.'+@factTableColumn + ' IN (SELECT D.C_BASECODE FROM DBO.I2B2 D JOIN DBO.METATREE A '+char(13)
							        + 'ON A.descendant = D.META_ID AND A.ANCESTOR='+CAST(@META_ID AS VARCHAR(10))+')' 
			/* additional predicate constraints */
			set @sql = @sql + 
			         case when @modifier_cd is not null then ' AND or'+cast(@or_items as varchar(2))+'.'+dbo.MOD_CONSTRAINT (@modifier_cd, @modifier_dim) else '' end
				   + case when not (@CONSTRAIN_OPERATOR IS NULL and @CONSTRAIN_VALUE IS NULL and @CONSTRAIN_TYPE IS NULL) 
										then ' AND or'+cast(@or_items as varchar(2))+'.'+ dbo.LAB_CONSTRAINT (@constrain_operator, @constrain_value, @constrain_type) else '' end
				   + case when (@panel_date_from is not null OR @panel_date_to is not null)
								           then ' AND or'+cast(@or_items as varchar(2))+'.'+dbo.TIME_CONSTRAINT (@panel_date_from, @panel_date_to) else '' end
		end
		else if @factTable = 'patient_dimension'
		begin
			set @sql = @sql +  'dbo.patient_dimension or'+cast(@or_items as varchar(2))+char(13)
			
			set @sql = @sql +  'on cpp.patient_num = or'+cast(@or_items as varchar(2))+'.patient_num'+char(13)
			set @sql = @sql + ' and (or'+cast(@or_items as varchar(2))+'.'+@columnName+' '+@operator+' '
			                +case when @operator = 'IN' then '('+@dimcode+')' else @dimcode end														 
						
		end

		
      end								          
	  
	  if @or_items_single_fact = 1 and @total_item_occur = 1 
	  begin
		set @sql = CHAR(13) + ' OR ' + 'or'+CAST(@or_items as varchar(2))
				   + case when @factTable <> 'concept_dimension' 
				     then '.'+@columnName+' '+@operator+' '+case when @operator = 'IN' then '('+@dimcode+')' else @dimcode end
				     else '.'+ @factTableColumn + ' IN (SELECT D.C_BASECODE FROM DBO.I2B2 D JOIN DBO.METATREE A '+char(13)
					      + 'ON A.descendant = D.META_ID AND A.ANCESTOR='+CAST(@META_ID AS VARCHAR(10))+')' end
				   + case when @modifier_cd is not null then ' AND or'+cast(@or_items as varchar(2))+'.'+dbo.MOD_CONSTRAINT (@modifier_cd, @modifier_dim) else '' end
				   + case when not (@CONSTRAIN_OPERATOR IS NULL and @CONSTRAIN_VALUE IS NULL and @CONSTRAIN_TYPE IS NULL) 
										then ' AND or'+cast(@or_items as varchar(2))+'.'+ dbo.LAB_CONSTRAINT (@constrain_operator, @constrain_value, @constrain_type) else '' end
				   + case when (@panel_date_from is not null OR @panel_date_to is not null)
								           then ' AND or'+cast(@or_items as varchar(2))+'.'+dbo.TIME_CONSTRAINT (@panel_date_from, @panel_date_to) else '' end            		
	  end	
	  
	  if @total_item_occur > 1
	  begin
		set @sql = 	char(13) + ' LEFT OUTER JOIN '
		+ case when @factTable = 'patient_dimension'
								   then '(SELECT v.PATIENT_NUM FROM '
								   + 'dbo.VISIT_DIMENSION v JOIN dbo.PATIENT_DIMENSION p ' +CHAR(13)
								   + ' on v.patient_num = p.patient_num AND p' 
								   + '.'+@columnName+' '+@operator+' '+case when @operator = 'IN' then '('+@dimcode+')' else @dimcode end
							       
							       when @factTable = 'concept_dimension' 
							       then '(SELECT PATIENT_NUM FROM '
							        + 'dbo.OBSERVATION_FACT ' + char(13)
									+ ' WHERE '+ @factTableColumn + ' IN (SELECT D.C_BASECODE FROM DBO.I2B2 D JOIN DBO.METATREE A '+char(13)
							        + 'ON A.descendant = D.META_ID AND A.ANCESTOR='+CAST(@META_ID AS VARCHAR(10))+')'
								    + case when @modifier_cd is not null then ' AND '+dbo.MOD_CONSTRAINT (@modifier_cd, @modifier_dim) else '' end
									+ case when not (@CONSTRAIN_OPERATOR IS NULL and @CONSTRAIN_VALUE IS NULL and @CONSTRAIN_TYPE IS NULL) 
										then ' AND or'+cast(@or_items as varchar(2))+'.'+ dbo.LAB_CONSTRAINT (@constrain_operator, @constrain_value, @constrain_type) else '' end
								    + case when (@panel_date_from is not null OR @panel_date_to is not null)
								           then ' AND '+dbo.TIME_CONSTRAINT (@panel_date_from, @panel_date_to) else '' end
								   when @factTable = 'visit_dimension'
								   then '(SELECT PATIENT_NUM FROM '
								   + 'dbo.VISIT_DIMENSION ' + char(13)
								   + ' WHERE ' + @factTableColumn + ' ' + @operator + case when @operator = 'IN' 
								                                                      then '(' + @dimcode + ')' else @dimcode end + char(13)
								   + case when (@panel_date_from is not null OR @panel_date_to is not null)
							              then ' AND '+dbo.TIME_CONSTRAINT (@panel_date_from, @panel_date_to) else '' end								                   
                                   end -- 34
                                 + case when @factTable='patient_dimension' then 'GROUP BY v.PATIENT_NUM HAVING COUNT(*) >= ' 
									    else 'GROUP BY PATIENT_NUM HAVING COUNT(*) >= ' end
                                 + CAST(@total_item_occur as varchar(3)) + char(13)
						         + ') or'+cast(@or_items as varchar(2)) + ' '
						         + 'on cpp.patient_num = or'+cast(@or_items as varchar(2))+'.patient_num'
	end						           
	  
	  
	return @sql
end





GO


